package com.br.objetos;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;

import com.br.outros.Conexao;
import com.br.outros.DateUtils;
import com.br.outros.MailSenderMobile;

public class CalculosMobile {

	private String sql = "";

	public boolean insertTempDistancias(String[] vet) {
		Connection con;
		// String URL = "jdbc:mysql://127.0.0.1:3306/test";
		// String USE = "root";
		String driver = Conexao.getDriver();
		String URL = Conexao.getURL();
		String USE = Conexao.getUSE();
		String PASS = Conexao.getPASS();
		// vet[0] = numeroCelular;
		// vet[1] = String.valueOf(CalculaDistancias.calculaDistancia(
		// vet[2] = String.valueOf(listaBares.get(i).getIdBar());
		// vet[3] = String.valueOf(idGrupo);
		// String vet[] = new String[2];
		// int len = lista.size();
		// int i = 0;
		Statement stmt;
		// String sqlTudo = "";
		try {
			Class.forName(driver);
			con = DriverManager.getConnection(URL, USE, PASS);
			// for (String[] vet2 : lista) {
			// while (i < (len)) {
			stmt = con.createStatement();

			sql = "INSERT INTO TEMPDISTANCIAS (IDGRUPO, IDBAR, NRCELULAR, DISTANCIA) VALUES"
					+ "(";
			// vet = ;
			sql += vet[3] + ",";
			sql += vet[2] + ",'";
			sql += vet[0] + "','";
			sql += vet[1] + "'); ";

			// con.commit();
			stmt.executeUpdate(sql);
			// sqlTudo+=sql;
			// System.out.println(sql);
			// i++;
			sql = "";
			// }
			// stmt.executeUpdate(sqlTudo);
			con.close();

			// }
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			MailSenderMobile mail = new MailSenderMobile();
			mail.enviaEmailsErro("ERRO CALCULOS MOBILE - insertTempDistancias<BR />\n"
					+ e.toString());
			return false;
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			MailSenderMobile mail = new MailSenderMobile();
			mail.enviaEmailsErro("ERRO CALCULOS MOBILE - insertTempDistancias<BR />\n"
					+ e.toString());
			return false;
		}
		return true;
	}

	public ArrayList<String[]> pegaDistanciasByGrupo(int idGrupo, int idBar) {
		// boolean res = false;
		Statement st = null;
		ResultSet rs = null;
		// String driver = "com.mysql.jdbc.Driver";
		Connection con;
		// String URL = "jdbc:mysql://localhost:3306/test";
		// String USE = "root";
		// String PASS = "";
		String driver = Conexao.getDriver();
		String URL = Conexao.getURL();
		String USE = Conexao.getUSE();
		String PASS = Conexao.getPASS();
		ArrayList<String[]> lista = new ArrayList<String[]>();
		try {
			Class.forName(driver);
			con = DriverManager.getConnection(URL, USE, PASS);
			st = con.createStatement();

			String sql = "SELECT IDGRUPO AS IDGRUPO, IDBAR AS IDBAR, NRCELULAR AS NRCELULAR, DISTANCIA AS DISTANCIA FROM TEMPDISTANCIAS WHERE IDGRUPO = "
					+ idGrupo + " AND IDBAR = " + idBar;

			// Buscar Alunos
			st = con.createStatement();
			rs = st.executeQuery(sql);

			while (rs.next()) {
				// bean.setBairro(rs.getString("BAIRRO"));
				String idgrupo = String.valueOf(rs.getInt("IDGRUPO"));
				String idbar = String.valueOf(rs.getInt("IDBAR"));
				String nrcelular = rs.getString("NRCELULAR");
				String distancia = rs.getString("DISTANCIA");
				String vet[] = new String[4];
				vet[0] = idgrupo;
				vet[1] = idbar;
				vet[2] = nrcelular;
				vet[3] = distancia;
				lista.add(vet);
			}
			// return id;
			// System.out.println(sql);
			con.close();

		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			MailSenderMobile mail = new MailSenderMobile();
			mail.enviaEmailsErro("ERRO CALCULOS MOBILE - pegaDistanciasByGrupo<BR />\n"
					+ e.toString());
		}
		return lista;
	}

	public int contBares() {
		// boolean res = false;
		Statement st = null;
		ResultSet rs = null;
		// String driver = "com.mysql.jdbc.Driver";
		Connection con;
		// String URL = "jdbc:mysql://localhost:3306/test";
		// String USE = "root";
		// String PASS = "";
		String driver = Conexao.getDriver();
		String URL = Conexao.getURL();
		String USE = Conexao.getUSE();
		String PASS = Conexao.getPASS();
		int res = 0;
		try {
			Class.forName(driver);
			con = DriverManager.getConnection(URL, USE, PASS);
			st = con.createStatement();

			String sql = "SELECT COUNT(*) AS CONTADORBAR FROM BAR";

			// Buscar Alunos
			st = con.createStatement();
			rs = st.executeQuery(sql);

			while (rs.next()) {
				res = rs.getInt("CONTADORBAR");
			}
			// return id;
			// System.out.println(sql);
			con.close();

		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			MailSenderMobile mail = new MailSenderMobile();
			mail.enviaEmailsErro("ERRO CALCULOS MOBILE - contBares<BR />\n"
					+ e.toString());
		}
		return res;
	}

	public int contGrupo(int idGrupo) {
		// boolean res = false;
		Statement st = null;
		ResultSet rs = null;
		// String driver = "com.mysql.jdbc.Driver";
		Connection con;
		// String URL = "jdbc:mysql://localhost:3306/test";
		// String USE = "root";
		// String PASS = "";
		String driver = Conexao.getDriver();
		String URL = Conexao.getURL();
		String USE = Conexao.getUSE();
		String PASS = Conexao.getPASS();
		int res = 0;
		try {
			Class.forName(driver);
			con = DriverManager.getConnection(URL, USE, PASS);
			st = con.createStatement();

			String sql = "SELECT COUNT(*) AS CONTADORGRUPO FROM CLIENTEGRUPO WHERE IDGRUPO = "
					+ idGrupo;

			// Buscar Alunos
			st = con.createStatement();
			rs = st.executeQuery(sql);

			while (rs.next()) {
				res = rs.getInt("CONTADORGRUPO");
			}
			// return id;
			// System.out.println(sql);
			con.close();

		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			MailSenderMobile mail = new MailSenderMobile();
			mail.enviaEmailsErro("ERRO CALCULOS MOBILE - contGrupo<BR />\n"
					+ e.toString());
		}
		return res;
	}

	public HashMap<String, ArrayList<BarBeanMobile>> getTop10(String telefone, int idGrupo) {
		// boolean res = false;
		Statement st = null;
		ResultSet rs = null;
		HashMap<String, ArrayList<BarBeanMobile>> mapTop10 = new HashMap<String, ArrayList<BarBeanMobile>>();
		ArrayList<BarBeanMobile> arrayTop10 = new ArrayList<BarBeanMobile>();
		System.out.println("-------------------\n"
				+ "ENTROU NO LISTAR BARES \n" + DateUtils.now() + "\n"
				+ "-------------------");

		// String driver = "com.mysql.jdbc.Driver";
		Connection con;
		// String URL = "jdbc:mysql://localhost:3306/test";
		// String USE = "root";
		// String PASS = "";
		String driver = Conexao.getDriver();
		String URL = Conexao.getURL();
		String USE = Conexao.getUSE();
		String PASS = Conexao.getPASS();
		ArrayList<String[]> lista = new ArrayList<String[]>();
		try {
			Class.forName(driver);
			con = DriverManager.getConnection(URL, USE, PASS);

			// PARA CADA GRUPO;

//			String sql = "SELECT DISTINCT IDGRUPO AS IDGRUPO FROM TEMPDISTANCIAS;";
//
//			st = con.createStatement();
//			rs = st.executeQuery(sql);
//			while (rs.next()) {
//				int idGrupo = (rs.getInt("IDGRUPO"));
			
				sql = "SELECT STATUS FROM TEMPMEDIAS WHERE STATUS = FALSE AND IDGRUPO = "+idGrupo;

				st = con.createStatement();
				rs = st.executeQuery(sql);
				CalculosMobile v = new CalculosMobile();
				if (!rs.next()) {
					v.insertMedias(idGrupo);
//				}
					v.insertMediasTrue(idGrupo);}
//			}
			//

			st = con.createStatement();

			sql = "SELECT IDCLIENTE AS IDCLIENTE FROM CLIENTE_CELULAR WHERE TELEFONE = '"
					+ telefone + "';";

			// Buscar Alunos
			st = con.createStatement();
			rs = st.executeQuery(sql);
			while (rs.next()) {
				int idCli = rs.getInt("IDCLIENTE");
				

				// pegar id grupo
				sql = "SELECT IDGRUPOCONVITE AS IDGRUPOCONVITE FROM CONVITE WHERE IDCLIENTE = "
						+ idCli
						+ " AND DATACONVITE = CURDATE() AND CONVITEFINALIZADO = TRUE;";

				// Buscar Alunos
				st = con.createStatement();
				rs = st.executeQuery(sql);

				// int idGrupo = 0;
				while (rs.next()) {
					int idGrupoConvite = rs.getInt("IDGRUPOCONVITE");

					sql = "SELECT IDGRUPO AS IDGRUPO FROM GRUPOCONVITE WHERE IDGRUPOCONVITE = "
							+ idGrupoConvite;

					// Buscar Alunos
					st = con.createStatement();
					rs = st.executeQuery(sql);

					// int idGrupo = 0;
					while (rs.next()) {
						
						//mudei aqui
						sql = "SELECT DISTINCT G.APELIDO AS GRUPO, B.IDBAR AS BAR, T.MEDIA AS MEDIA FROM TEMPMEDIAS T JOIN GRUPO G ON (G.IDGRUPO = T.IDGRUPO) JOIN BAR B ON (B.STATUS = 1 AND B.IDBAR = T.IDBAR) WHERE T.STATUS = 1 AND T.IDGRUPO = "
								+ idGrupo + " ORDER BY T.MEDIA LIMIT 10; ";

						// Buscar Alunos
						st = con.createStatement();
						rs = st.executeQuery(sql);

						while (rs.next()) {
							String vet[] = new String[3];
							String grupo = rs.getString("GRUPO");
							String bar = rs.getString("BAR");
							String media = rs.getString("MEDIA");
//							String idMedias = rs.getString("IDMEDIAS");
							vet[0] = grupo;
							vet[1] = bar;
							vet[2] = media;

							lista.add(vet);

							Statement stmt = con.createStatement();
							//MUDEI AQUI
//							sql = "UPDATE TEMPMEDIAS SET STATUS = FALSE WHERE IDGRUPO = "+grupo+" AND MEDIA = "+media+" AND IDBAR = "
//									+ bar;
//
//							// con.commit();
//							stmt.executeUpdate(sql);

						}
						// return id;
						// System.out.println(sql);
					}
				}
			}
			for (String[] aux : lista) {
				BarMobile bar = new BarMobile();
				BarBeanMobile bean = new BarBeanMobile();
				bean = bar.getBar(Integer.valueOf(aux[1]));
				bean.setMedia(Double.valueOf(aux[2]));
				// pegar promocoes por bar
				// descricao - R$ valor
				sql = "SELECT DESCRICAO AS DESCRICAO, VALOR AS VALOR, DATAVALIDADE AS DATAVALIDADE FROM PROMOCAO WHERE CURDATE() <= DATAVALIDADE  AND STATUS = TRUE AND IDBAR = "
						+ aux[1] + "; ";

				// Buscar Alunos
				st = con.createStatement();
				rs = st.executeQuery(sql);
				String res = "";
				while (rs.next()) {
					res += rs.getString("DESCRICAO") + " - R$ "
							+ rs.getDouble("VALOR") + ";";

				}
				bean.setPromocao(res);
				 bean.setIdGrupo(idGrupo);
				bean.setAmigos("");
				arrayTop10.add(bean);
			}

			mapTop10.put("arrayBares", arrayTop10);
			con.close();

		} catch (Exception e) {
			e.printStackTrace();
			MailSenderMobile mail = new MailSenderMobile();
			mail.enviaEmailsErro("ERRO CALCULOS MOBILE - getTop10<BR />\n"
					+ e.toString());
		}
		return mapTop10;
	}
	
	public boolean verificaNotificacaoBares(String telefone, int idGrupo) {
		// boolean res = false;
		Statement st = null;
		ResultSet rs = null;

		// String driver = "com.mysql.jdbc.Driver";
		Connection con;
		// String URL = "jdbc:mysql://localhost:3306/test";
		// String USE = "root";
		// String PASS = "";
		String driver = Conexao.getDriver();
		String URL = Conexao.getURL();
		String USE = Conexao.getUSE();
		String PASS = Conexao.getPASS();
		try {
			Class.forName(driver);
			con = DriverManager.getConnection(URL, USE, PASS);

				//DELETADO WHILE
				int idCliente = ClienteMobile.getIdByCliente(telefone);
			
				sql = "SELECT TM.STATUS FROM TEMPMEDIAS TM JOIN CLIENTEGRUPO CG ON (CG.IDGRUPO = TM.IDGRUPO AND CG.IDCLIENTE = "+idCliente+") WHERE STATUS = TRUE AND TM.IDGRUPO = "+idGrupo+";";

				st = con.createStatement();
				rs = st.executeQuery(sql);
				if (rs.next()) {
					return false;
				}

			
			
			con.close();
		} catch (Exception e) {
			e.printStackTrace();
			MailSenderMobile mail = new MailSenderMobile();
			mail.enviaEmailsErro("ERRO CALCULOS MOBILE - getTop10<BR />\n"
					+ e.toString());
		}
		return true;
	}

	public ArrayList<String[]> calculaMedias(int idGrupo) {
		// PEGAR OS GRUPOS DO TEMPDISTANCIAS
		// String URL = "jdbc:mysql://127.0.0.1:3306/test";
		// String USE = "root";
		String driver = Conexao.getDriver();
		String URL = Conexao.getURL();
		String USE = Conexao.getUSE();
		String PASS = Conexao.getPASS();
		// vet[0] = idbar;
		// vet[1] = media;
		// vet[2] = idgrupo;
		// String sqlTudo = "";
		Connection con;
		ArrayList<String[]> somasPorBar = new ArrayList<String[]>();

		try {
			Class.forName(driver);
			con = DriverManager.getConnection(URL, USE, PASS);
			// for (String[] vet2 : lista) {
			// while (i < (len)) {

			String sql = "SELECT IDBAR AS IDBAR, (SUM(DISTANCIA) / (SELECT COUNT(DISTINCT NRCELULAR) FROM TEMPDISTANCIAS WHERE IDGRUPO = "
					+ idGrupo
					+ ") ) AS MEDIA, IDGRUPO AS IDGRUPO FROM TEMPDISTANCIAS GROUP BY IDBAR, IDGRUPO;";

			Statement st = con.createStatement();
			ResultSet rs = st.executeQuery(sql);

			while (rs.next()) {
				String vet[] = new String[3];
				String grupo = rs.getString("IDGRUPO");
				String bar = rs.getString("IDBAR");
				String media = rs.getString("MEDIA");
				vet[0] = grupo;
				vet[1] = bar;
				vet[2] = media;

				somasPorBar.add(vet);
			}

			// i++;
			sql = "";

			// }
			// stmt.executeUpdate(sqlTudo);
			con.close();

			// }
		} catch (Exception e) {
			e.printStackTrace();
			MailSenderMobile mail = new MailSenderMobile();
			mail.enviaEmailsErro("ERRO CALCULOS MOBILE - calculaMedias<BR />\n"
					+ e.toString());
		}

		return somasPorBar;
	}

	public boolean insertMedias(int idGrupo) {
		ArrayList<String[]> lista = calculaMedias(idGrupo);
		Connection con;
		// String URL = "jdbc:mysql://127.0.0.1:3306/test";
		// String USE = "root";
		String driver = Conexao.getDriver();
		String URL = Conexao.getURL();
		String USE = Conexao.getUSE();
		String PASS = Conexao.getPASS();
		// vet[0] = grupo;
		// vet[1] = bar;
		// vet[2] = media;
		Statement stmt;
		// String sqlTudo = "";
		try {
			Class.forName(driver);
			con = DriverManager.getConnection(URL, USE, PASS);
			// for (String[] vet2 : lista) {
			// while (i < (len)) {
			for (String[] vet : lista) {

				stmt = con.createStatement();

				sql = "INSERT INTO TEMPMEDIAS (IDGRUPO, IDBAR, MEDIA,STATUS) VALUES ("
						+ "";
				// vet = ;
				sql += vet[0] + ",";
				sql += vet[1] + ",";
				sql += vet[2] + ", FALSE); ";

				// con.commit();
				stmt.executeUpdate(sql);
				// sqlTudo+=sql;
				// System.out.println(sql);

				// i++;
				sql = "";
			}
			// }
			// stmt.executeUpdate(sqlTudo);
			con.close();

			// }
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			MailSenderMobile mail = new MailSenderMobile();
			mail.enviaEmailsErro("ERRO CALCULOS MOBILE - insertMedias<BR />\n"
					+ e.toString());
			return false;
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			MailSenderMobile mail = new MailSenderMobile();
			mail.enviaEmailsErro("ERROS CALCULOS MOBILE - insertMedias<BR />\n"
					+ e.toString());
			return false;
		}
		return true;
	}

	public boolean insertMediasTrue(int idGrupo) {
		ArrayList<String[]> lista = calculaMedias(idGrupo);
		Connection con;
		// String URL = "jdbc:mysql://127.0.0.1:3306/test";
		// String USE = "root";
		String driver = Conexao.getDriver();
		String URL = Conexao.getURL();
		String USE = Conexao.getUSE();
		String PASS = Conexao.getPASS();
		// vet[0] = grupo;
		// vet[1] = bar;
		// vet[2] = media;
		Statement stmt;
		// String sqlTudo = "";
		try {
			Class.forName(driver);
			con = DriverManager.getConnection(URL, USE, PASS);
			// for (String[] vet2 : lista) {
			// while (i < (len)) {
			for (String[] vet : lista) {

				stmt = con.createStatement();

				sql = "UPDATE TEMPMEDIAS SET STATUS = TRUE WHERE IDGRUPO = "
						+ idGrupo;

				// con.commit();
				stmt.executeUpdate(sql);
				// sqlTudo+=sql;
				// System.out.println(sql);

				// i++;
				sql = "";
			}
			// }
			// stmt.executeUpdate(sqlTudo);
			con.close();

			// }
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			MailSenderMobile mail = new MailSenderMobile();
			mail.enviaEmailsErro("ERRO CALCULOS MOBILE - insertMedias<BR />\n"
					+ e.toString());
			return false;
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			MailSenderMobile mail = new MailSenderMobile();
			mail.enviaEmailsErro("ERROS CALCULOS MOBILE - insertMedias<BR />\n"
					+ e.toString());
			return false;
		}
		return true;
	}
}
